package com.hs.chd.action.stat;
 
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.hs.chd.helper.ChangeCharset;
import com.hs.chd.service.IDataService;
import com.hs.core.action.BaseAction;
import com.hs.core.db.DBMgr;
@Controller   
public class StatController  extends BaseAction {
    private static final Logger logger = Logger.getLogger(StatController.class);
	private IDataService dataService;
	@Autowired  
	public void setDataService(IDataService dataService) {
		this.dataService = dataService;
	}
	/**
	  * 临床数据分析主页
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/stat/statindex.do")   
   public ModelAndView statindex(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 String searchtype = request.getParameter("searchtype");
		 String qstr = request.getQueryString();
		 Map parammap = null;
		 if("basic_search".equals(searchtype)){
			 parammap = basicSearchPrepare(request);
		 }else if("advsearch".equals(searchtype)) {
			 parammap = this.advSearchPrepare(request);
		 }
		 StringBuffer allfullsql = (StringBuffer)parammap.get("allfullsql");//request.getParameter("allsql"); 
		 String rangesql = allfullsql.toString();
		 if(null != rangesql && rangesql.indexOf("order by")!=-1){
			 rangesql = rangesql.substring(0,rangesql.indexOf("order by"));
		 }
		 //String allsql = "select * from TCM_CLINIC_JMZ_YFLF where  ZLGC_ID in(select a.JTZLGC_ID from TCM_CLINIC_JMZ_JTZLGC a,(select LCYJ_ID from SCHD_PAPER_RELATION where pid in(select id from " + rangesql + ")) b where a.LCYJ_ID=b.LCYJ_ID ) ";
		 String allsql = "SELECT * FROM (select fjmc,count(*) as countnum from (select yfmc as fjmc from SCHD_TCM_CLINIC_C_YFLF where yw_id in (select d.LCYJ_ID from  ("+rangesql+") c,SCHD_PAPER_RELATION d where c.id=d.pid )) group by fjmc order by count(*) desc) WHERE ROWNUM<=100";
		 DBMgr dbo = DBMgr.getDbUtilInst(request.getSession().getServletContext());
		 List datalist = dbo.queryList(allsql, null);
		 modelMap.put("datalist", datalist);
		 modelMap.put("allsql", rangesql);
		 System.out.println("sql:" + rangesql);
		 modelMap.put("searchtype", searchtype);
		
 		 return new ModelAndView("app/stat/statindex",modelMap);
		 
  } 
	 
	 
	 /**
	  * 临床数据相关方剂分析
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/stat/prestat.do")   
   public ModelAndView prestat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException { 
		 String rangesql = request.getParameter("allsql");  
		 //String allsql = "select * from TCM_CLINIC_JMZ_YFLF where  ZLGC_ID in(select a.JTZLGC_ID from TCM_CLINIC_JMZ_JTZLGC a,(select LCYJ_ID from SCHD_PAPER_RELATION where pid in(select id from " + rangesql + ")) b where a.LCYJ_ID=b.LCYJ_ID ) ";
		 String allsql = "SELECT * FROM (select fjmc,count(*) as countnum from (select yfmc as fjmc from SCHD_TCM_CLINIC_C_YFLF where yw_id in (select d.LCYJ_ID from  ("+rangesql+") c,SCHD_PAPER_RELATION d where c.id=d.pid )) group by fjmc order by count(*) desc) WHERE ROWNUM<=100";
		 DBMgr dbo = DBMgr.getDbUtilInst(request.getSession().getServletContext());
		 List datalist = dbo.queryList(allsql, null);
		 modelMap.put("datalist", datalist);
		 modelMap.put("allsql", rangesql);
		 return new ModelAndView("app/stat/prestat",modelMap);
		 
  } 
	 /**
	  * 临床数据相关疾病证候分析
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/stat/disstat.do")   
   public ModelAndView disstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 String rangesql = request.getParameter("allsql");  
		 //String allsql = "select * from TCM_CLINIC_JMZ_YFLF where  ZLGC_ID in(select a.JTZLGC_ID from TCM_CLINIC_JMZ_JTZLGC a,(select LCYJ_ID from SCHD_PAPER_RELATION where pid in(select id from " + rangesql + ")) b where a.LCYJ_ID=b.LCYJ_ID ) ";
		 String allsql = "SELECT * FROM (select fjmc,count(*) as countnum from (select BZZMC as fjmc from SCHD_TCM_CLINIC_C_LCZL where BZZMC is not null and yw_id in (select d.LCYJ_ID from  ("+rangesql+") c,SCHD_PAPER_RELATION d where c.id=d.pid )) group by fjmc order by count(*) desc) WHERE ROWNUM<=100";
		 DBMgr dbo = DBMgr.getDbUtilInst(request.getSession().getServletContext());
		 List datalist = dbo.queryList(allsql, null);
		 modelMap.put("datalist", datalist);
		 modelMap.put("allsql", rangesql);
		 return new ModelAndView("app/stat/disstat",modelMap);
		 
  } 
	 /**
	  * 临床数据相关西医药物分析
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/stat/westmedstat.do" )   
   public ModelAndView westmedstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 String rangesql = request.getParameter("allsql");  
		 //String allsql = "select * from TCM_CLINIC_JMZ_YFLF where  ZLGC_ID in(select a.JTZLGC_ID from TCM_CLINIC_JMZ_JTZLGC a,(select LCYJ_ID from SCHD_PAPER_RELATION where pid in(select id from " + rangesql + ")) b where a.LCYJ_ID=b.LCYJ_ID ) ";
		 String allsql = "SELECT * FROM (select fjmc,count(*) as countnum from (select ywmc as fjmc from SCHD_TCM_CLINIC_C_XYLF where ywmc is not null and  yw_id in (select d.LCYJ_ID from  ("+rangesql+") c,SCHD_PAPER_RELATION d where c.id=d.pid )) group by fjmc order by count(*) desc) WHERE ROWNUM<=100";
		 DBMgr dbo = DBMgr.getDbUtilInst(request.getSession().getServletContext());
		 List datalist = dbo.queryList(allsql, null);
		 modelMap.put("datalist", datalist);
		 modelMap.put("allsql", rangesql);
		 return new ModelAndView("app/stat/westmedstat",modelMap);
		 
  } 
	 /**
	  * 临床数据相关穴位分析
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/stat/pointstat.do" )   
   public ModelAndView pointstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 String rangesql = request.getParameter("allsql");  

		 //String allsql = "select * from TCM_CLINIC_JMZ_YFLF where  ZLGC_ID in(select a.JTZLGC_ID from TCM_CLINIC_JMZ_JTZLGC a,(select LCYJ_ID from SCHD_PAPER_RELATION where pid in(select id from " + rangesql + ")) b where a.LCYJ_ID=b.LCYJ_ID ) ";
		 String  allsql = "SELECT * FROM (select fjmc,count(*) as countnum from (select  xwmc as fjmc from SCHD_TCM_XWXX WHERE yw_id in (select d.LCYJ_ID from  ("+rangesql+") c,SCHD_PAPER_RELATION d where c.id=d.pid ))  group by fjmc order by count(*) desc) WHERE ROWNUM<=100";
		 DBMgr dbo = DBMgr.getDbUtilInst(request.getSession().getServletContext());
		 List datalist = dbo.queryList(allsql, null);
		 modelMap.put("datalist", datalist);
		 modelMap.put("allsql", rangesql);
		 return new ModelAndView("app/stat/pointstat",modelMap);
		 
  } 
	 /**
	  * 临床数据相关中药分析
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/stat/herbstat.do" )   
   public ModelAndView herbstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 String rangesql = request.getParameter("allsql");  
 		 String allsql = "SELECT * FROM (select fjmc,count(*) as countnum from (select ywmc as fjmc from SCHD_TCM_YWXX WHERE yw_id in (select d.LCYJ_ID from  ("+rangesql+") c,SCHD_PAPER_RELATION d where c.id=d.pid )) group by fjmc order by count(*) desc) WHERE ROWNUM<=100";
		 DBMgr dbo = DBMgr.getDbUtilInst(request.getSession().getServletContext());
		 List datalist = dbo.queryList(allsql, null);
		 modelMap.put("datalist", datalist);
		 modelMap.put("allsql", rangesql);
		 return new ModelAndView("app/stat/herbstat",modelMap);
		 
  } 
	 private Map advSearchPrepare(HttpServletRequest request){
		 try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
		 
		}  

		 Map paramMap = new HashMap();
		 String orderName = request.getParameter("orderName");//排序字段
		 String orderNum = request.getParameter("orderNum");//排序方式
		 String searchtype = request.getParameter("searchtype");//排序方式
		 String datatypestr = request.getParameter("datatype");//数据范围
		 String keywordstr = request.getParameter("keyword");//关键词
 		 String startyear = request.getParameter("startyear_adv");//起始年份
		 String endyear = request.getParameter("endyear_adv");//起始年份  
		 String disnamestr = request.getParameter("disnamestr");//起始年份  
		 
		 request.setAttribute("orderName", orderName); 
		 request.setAttribute("orderNum", orderNum); 
		 request.setAttribute("searchtype", searchtype); 
		 StringBuffer orderByStr = new StringBuffer(" order by ");
		 if(StringUtils.isNotBlank(orderName)){
		    	if("1".equals(orderNum)){
		    		orderByStr.append(orderName).append(" asc");
		    	}
		    	if("2".equals(orderNum)){
		    		orderByStr.append(orderName).append(" desc");
		    	}
		  }else{
		    	orderByStr.append("a.title").append(" desc");
		    	orderName = "a.title";
		    	orderNum = "2";
		  }
		 StringBuffer allfullsql= new StringBuffer();
		 allfullsql.append("select a.* from SEARCHMVIEW a where 1=1    ");
		 String[] datatypes = null;
 		 if(null != datatypestr && !"".equals(datatypestr)){
			 datatypes = datatypestr.split(",");
		 }
 		 System.out.println("datatypestr:" + datatypestr);
 		 if(null != datatypes && datatypes.length >0){
  			  int len = datatypes.length;
 			  if(len == 1){
 				 allfullsql.append("  and  a.DATATYPE=").append(datatypes[0]);
 				 System.out.println("000:" + datatypes[0]);
 			  }else  if(len == 2){
 				 System.out.println("000:" + datatypes[0]);
 		 		 System.out.println("000:" + datatypes[1]); 
 				 allfullsql.append("  and  (a.DATATYPE =");
 				 allfullsql.append(datatypes[0]);
 				 allfullsql.append(" or ");
 				 allfullsql.append(" a.DATATYPE =");
 				 allfullsql.append(datatypes[1]);
 				 allfullsql.append(" ) ");
 			  }else  if(len == 3){
 				 System.out.println("000:" + datatypes[0]);
 		 		 System.out.println("000:" + datatypes[1]);
 		 		 System.out.println("000:" + datatypes[2]);
  				 allfullsql.append("  and (a.DATATYPE =");
  				 allfullsql.append(datatypes[0]);
  				 allfullsql.append(" or ");
  				 allfullsql.append(" a.DATATYPE =");
  				 allfullsql.append(datatypes[1]);
  				 allfullsql.append("  or ");
  				 allfullsql.append(" a.DATATYPE =");
 				 allfullsql.append(datatypes[2]);
  				 allfullsql.append(" ) ");
  			  } 
		 }else{
			 allfullsql.append("  and  a.DATATYPE=0 ");
		 }
 		
 		if(StringUtils.isNotEmpty(disnamestr)){
			 String[] disnamearr = disnamestr.split(",");
			 if(null != disnamearr && disnamearr.length == 1){
				 allfullsql.append("  and  a.DISNAME='").append(disnamearr[0]).append("' ");
			 }
			 if(null != disnamearr && disnamearr.length == 2){
				 allfullsql.append("  and  (a.DISNAME='").append(disnamearr[0]).append("' ");
				 allfullsql.append("  or  a.DISNAME='").append(disnamearr[1]).append("') ");
			 }
			  
		 }
		 if(StringUtils.isNotEmpty(startyear)){
			 allfullsql.append("  and  to_date(substr(a.YEARSTR,0,4),'yyyy')>=").append("to_date("+startyear+",'yyyy') ");
		 }
		 if(StringUtils.isNotEmpty(endyear)){
			 allfullsql.append("  and  to_date(substr(a.YEARSTR,0,4),'yyyy')<=").append("to_date("+endyear+",'yyyy') ");
		 }
		 
 		 StringBuffer keywordbuffer = new StringBuffer();
		 //拼接sql
 		 
 		 StringBuffer conditionsql = new StringBuffer();
 		 conditionsql.append(createSql(request,"txt_1",null,keywordbuffer));
 		 conditionsql.append(createSql(request,"txt_2",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_3",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_4",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_5",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_6",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_7",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_8",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_9",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_10",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_11",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_12",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_13",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_14",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_15",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_16",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_17",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_18",null,keywordbuffer));
		 conditionsql.append(createSql(request,"txt_19",null,keywordbuffer));
		 System.out.println("conditionsql:" + conditionsql);
		 if(!"".equals(conditionsql.toString())){ 
			 allfullsql.append(" and ");
			 allfullsql.append("(");
			 allfullsql.append(conditionsql.substring(4,conditionsql.length()));
			 allfullsql.append(") ");
		 }
		 allfullsql.append(orderByStr); 
		//翻页时使用从页面传过来的关键词
		 if("".equals(keywordbuffer.toString())&&StringUtils.isNotEmpty(keywordstr)){
			 keywordbuffer.append(keywordstr);
		 }
//		 
		 String keyword = keywordbuffer.toString(); 
	     if(keyword.endsWith("|")){
	    	 keyword = keyword.substring(0,keyword.length()-1);
	     }
	     
	     paramMap.put("allfullsql", allfullsql); 
	     paramMap.put("orderName", orderName);
	     paramMap.put("orderNum", orderNum); 
	     paramMap.put("keyword", keyword); 
		 return paramMap;
	 }

	 private String createSql(HttpServletRequest request,String cname,StringBuffer buffer,StringBuffer keywordbuffer){
		 StringBuffer sqlbuffer = new StringBuffer();
		 //值
		 String c_value = cname+"_value";
		 //逻辑关系，并且 或者
		 String c_logical = cname+"_logical";
		 //字段的名字
		 String c_sel = cname+"_sel";
		 //精确或模糊
		 String c_special = cname+"_special";
		 
		 String cvalue = request.getParameter(c_value);
		 String clogical = request.getParameter(c_logical);
		 String csel = request.getParameter(c_sel);
		 String cspecial = request.getParameter(c_special);
		 
		 String logicalname = "";
		 
		// String colname = (String)CONSTANTMAP.get(csel);
//		 if(null != colname){
//			 if("1".equals(clogical)){
//				 logicalname = " OR ";
//				 
//			 }else{
//				 logicalname = " AND ";
//			 }
//			// buffer.append(colname ).append("“").append(cvalue).append("”&nbsp;").append(logicalname).append("&nbsp;");
//			 keywordbuffer.append(cvalue).append("|");
//		 }
		 request.setAttribute(c_value, cvalue);
		 request.setAttribute(c_logical, c_logical);
		 request.setAttribute(c_sel, c_sel);
		 request.setAttribute(c_special, c_special);
		 //logger.debug("value:::" + clogical + "," + csel + "," + cvalue + "," + cspecial);
//		 if(StringUtils.isEmpty(cspecial))
//			 cspecial = "1";//默认为模糊检索
		 if(null == cvalue || StringUtils.isEmpty(cvalue)){//查询条件为空时返回空
			 return "";
		 }
		 logger.debug("clogical:" + clogical);
		 logger.debug("c_sel:" + c_sel);
		 if("0".equals(clogical)){
			 //0 and 关系  1：或者关系
			 sqlbuffer.append(" and "); 
		 }else if("1".equals(clogical)){
			 //0 and 关系  1：或者关系
			 sqlbuffer.append(" or "); 
		 }else{
			 sqlbuffer.append(" and "); 
		 }
		 sqlbuffer.append("lower(").append(csel).append(")");
		 if("0".equals(cspecial)){
			 //0 精确  1：模糊
			 sqlbuffer.append(" =lower('"); 
			 sqlbuffer.append(cvalue.trim());
			 sqlbuffer.append("')");
		 }
		 if("1".equals(cspecial)){
			//0 精确  1：模糊
			 sqlbuffer.append(" like '%'||lower('"); 
			 sqlbuffer.append(cvalue.trim());
			 sqlbuffer.append("')||'%'");
		 }
		 return sqlbuffer.toString();
	 }
	 private Map basicSearchPrepare(HttpServletRequest request){
    	 Map param = new HashMap();
    	 String basiccol = request.getParameter("basiccol");//字段列名
		 String basicvalue = request.getParameter("basicvalue");//字段值
		 String basicspecial = request.getParameter("basicspecial");//查询方式：精确，模糊
 		 String orderName = request.getParameter("orderName");//排序字段
		 String orderNum = request.getParameter("orderNum");//排序方式
		 String onesql = request.getParameter("onesql");//一次检索sql 
		 String disnamestr = request.getParameter("disnamestr");//一次检索sql 
		 String datatype = request.getParameter("datatype");//数据范围
		 String onekeyword = request.getParameter("onekeyword");//二次检索中第一次的检索词
		 String onesearchinfo = request.getParameter("onesearchinfo");//二次检索中第一次的检索词
		 String issyn = request.getParameter("issyn");//二次检索中第一次的检索词
  		 request.setAttribute("orderName", orderName); 
		 request.setAttribute("orderNum", orderNum); 
 		 String startyear = request.getParameter("startyear");//起始年份
		 String endyear = request.getParameter("endyear");//起始年份 
		 StringBuffer orderByStr = new StringBuffer(" order by ");
		 if(StringUtils.isNotBlank(orderName)){
		    	if("1".equals(orderNum)){
		    		orderByStr.append(orderName).append(" asc");
		    	}
		    	if("2".equals(orderNum)){
		    		orderByStr.append(orderName).append(" desc");
		    	}
		  }else{
		    	orderByStr.append("a.title").append(" desc");
		    	orderName = "a.title";
		    	orderNum = "2";
		  }
		 StringBuffer allfullsql= new StringBuffer();
		 String keyword = null;
		 if(StringUtils.isEmpty( datatype)){
			 //0 代表全部三种数据类型
			 datatype = "0";
		 }
		 allfullsql.append("select a.* from SEARCHMVIEW a where 1=1    ");
		 if(StringUtils.isNotEmpty(datatype)){
			 if(!"0".equals(datatype)){
				 allfullsql.append("  and  a.DATATYPE=").append(datatype);
			 } 
		 }
		 if(StringUtils.isNotEmpty(disnamestr)){
			 String[] disnamearr = disnamestr.split(",");
			 if(null != disnamearr && disnamearr.length == 1){
				 allfullsql.append("  and  a.DISNAME='").append(disnamearr[0]).append("' ");
			 }
			 if(null != disnamearr && disnamearr.length == 2){
				 allfullsql.append("  and  (a.DISNAME='").append(disnamearr[0]).append("' ");
				 allfullsql.append("  or  a.DISNAME='").append(disnamearr[1]).append("') ");
			 }
			  
		 }
		 if(StringUtils.isNotEmpty(startyear)){
			 allfullsql.append("  and  to_date(substr(a.YEARSTR,0,4),'yyyy')>=").append("to_date("+startyear+",'yyyy') ");
		 }
		 if(StringUtils.isNotEmpty(endyear)){
			 allfullsql.append("  and  to_date(substr(a.YEARSTR,0,4),'yyyy')<=").append("to_date("+endyear+",'yyyy') ");
		 }
		 if(StringUtils.isNotEmpty(basicvalue)){
			 keyword = basicvalue;
			 if("0".equals(basicspecial)){
				 if("TITLE".equals(basiccol)){
					 allfullsql.append(" and (lower(a.").append(basiccol).append(")=lower('").append(basicvalue.trim()).append("')");
					 allfullsql.append(" or lower(a.TITLEN) =lower('").append(basicvalue.trim()).append("'))");
				 }else if("ORIGINALTEXT".equals(basiccol)||"ABSTRACT".equals(basiccol)){
					 allfullsql.append(" and lower(a.").append("ABSTRACT)").append("=lower('").append(basicvalue.trim()).append("')");
					 //allfullsql.append(" or lower(a.ORIGINALTEXT) =lower('").append(basicvalue.trim()).append("'))");
				 }else if("ALL".equals(basiccol)){//任意字段检索
					 allfullsql.append(allColumnsearch(basicvalue,basicspecial));
				 }
				 //由于没有同义词关联，只能查询两个
//				 else if("DISNAME".equals(basiccol)){//疾病名称检索
//					 if("1".equals(issyn)){
//						 allfullsql.append(" and a.DATATYPE=1  and a.DISNAME in ( ");
//						 allfullsql.append(" select C.RIGHTNAME  from   SCHD_STANDWORD C WHERE   C.OTHERNAME = '"+basicvalue.trim()+"'");
//						 
//						 allfullsql.append(" UNION all select C.OTHERNAME  from   SCHD_STANDWORD C WHERE   C.RIGHTNAME =  = '"+basicvalue.trim()+"')"); 
//						
//					 }else{
//						 allfullsql.append(" and a.id in (select PAPERID from CHD_PAPERTAGS where tagid in(");
//						 allfullsql.append(" SELECT id FROM CHD_TAGS WHERE  TAGNAME = '"+basicvalue.trim()+"'))");
//					 }
// 				 }
				 else{
					  //String colname = CONSTANTMAP.get(basiccol)+""; 
				      //searchbuffer.append(colname).append("“").append(basicvalue).append("”").append("&nbsp;"); 

					 allfullsql.append(" and lower(a.").append(basiccol).append(")=lower('").append(basicvalue.trim()).append("')");
				 }
				 
			 }else if("1".equals(basicspecial)){
				 if("TITLE".equals(basiccol)){
					 allfullsql.append(" and (lower(a.").append(basiccol).append(") like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
					 allfullsql.append(" or lower(a.TITLEN) like '%'||lower('").append(basicvalue.trim()).append("')||'%')");
 
				 }else if("PERIODICALNAME".equals(basiccol)||"BOOKNAME".equals(basiccol)){
					 allfullsql.append(" and lower(a.").append("PERIODICALNAME").append(") like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
  
				 }else if("ABSTRACT".equals(basiccol)||"ORIGINALTEXT".equals(basiccol)){
					 allfullsql.append(" and lower(a.").append("ABSTRACT)").append(" like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
					// allfullsql.append(" or lower(a.ORIGINALTEXT) like '%'||lower('").append(basicvalue.trim()).append("')||'%')");
 
				 }
//				 else if("DISNAME".equals(basiccol)){//疾病名称检索
//					 if("1".equals(issyn)){
//						 allfullsql.append(" and a.DATATYPE=1   and a.DISNAME in ( ");
//						 allfullsql.append(" UNION all select C.RIGHTNAME  from   SCHD_STANDWORD C WHERE   C.OTHERNAME like '%"+basicvalue.trim()+"%')");
//						 allfullsql.append(" UNION all SELECT id from CHD_TAGS WHERE TAGNAME LIKE '%"+basicvalue.trim()+"%')");
//					 }else{
//						 allfullsql.append(" and a.id in (select PAPERID from CHD_PAPERTAGS where tagid in(");
//						 allfullsql.append(" SELECT id FROM CHD_TAGS WHERE TAGNAME LIKE '%"+basicvalue.trim()+"%'))");
//					 }
// 				 }
				 else if("ALL".equals(basiccol)){//任意字段检索
					 allfullsql.append(allColumnsearch(basicvalue,basicspecial));
 				 }else{
					   
					  allfullsql.append(" and lower(a.").append(basiccol).append(") like '%'||lower('").append(basicvalue.trim()).append("')||'%' ");
				 }
			 }
		 } 
		
		 if(StringUtils.isNotEmpty(onesql)){
			 onesql = "select id from(" + onesql + ")";
			 allfullsql.append(" and a.id in (");
			 allfullsql.append(onesql);
			 allfullsql.append(")");
			 if(null!= keyword){
				 keyword += "|"+onekeyword;
			 }else{ 
			 	 keyword = onekeyword;
			 }
		 }else{
			 onekeyword = keyword;
		 }
		 allfullsql.append(orderByStr);
		 param.put("allfullsql", allfullsql);
		 param.put("basiccol", basiccol);
		 param.put("basicvalue", basicvalue);
		 param.put("basicspecial", basicspecial);
		 param.put("orderName", orderName);
		 param.put("orderNum", orderNum);
		 param.put("datatype", datatype);
		 param.put("keyword", keyword);
		 param.put("onekeyword", onekeyword); 
		 param.put("onesearchinfo", onesearchinfo);
		 
		 return param;
     }
	 /**
	  * 任意字段sql拼接
	  * @return
	  */
	 private static String allColumnsearch(String basicvalue ,String searchtype){
		 StringBuilder builder = new StringBuilder();
		 if("1".equals(searchtype)){
	 		 //标题、关键词、主题词、摘要/原文文本化、刊名、英文刊名、书籍名称、年份/朝代、期、基金、资助类别\作者、英文作者
			 builder.append(" and (lower(a.TITLE").append(") like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 builder.append(" or lower(a.TITLEN) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
	 		 builder.append(" or lower(a.").append("PERIODICALNAME").append(")like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			// builder.append(" or lower(a.PERIODICALNAMEENG) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 //builder.append(" or lower(a.BOOKNAME) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
	 		 builder.append(" or lower(a.").append("ABSTRACT)").append("like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 //builder.append(" or lower(a.ABSTRACTENG) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			// builder.append(" or lower(a.ORIGINALTEXT) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
	 		 builder.append(" or lower(a.").append("SUBJECTWORD)").append(" like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 //builder.append(" or lower(a.SUBJECTWORDENG) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
	  		 builder.append(" or lower(a.").append("KEYWORD)").append(" like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			// builder.append(" or lower(a.KEYWORDENG) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 builder.append(" or lower(a.AUTHOR) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			// builder.append(" or lower(a.YEARSTR) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 //builder.append(" or lower(a.AUTHORUNIT) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			// builder.append(" or lower(a.AUTHORADDRESS) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");

			 builder.append(" or lower(a.PERIOD) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 builder.append(" or lower(a.BOOKCD) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 builder.append(" or lower(a.FUNDNAME) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 builder.append(" or lower(a.FUNDTYPE) like '%'||lower('").append(basicvalue.trim()).append("')||'%'");
			 builder.append(" or lower(a.TCMNAME) like '%'||lower('").append(basicvalue.trim()).append("')||'%')"); 

		 }else if("0".equals(searchtype)){
			  
	 		 //标题、关键词、主题词、摘要/原文文本化、刊名、英文刊名、书籍名称、年份/朝代、期、基金、资助类别\作者、英文作者
			 builder.append(" and (lower(a.TITLE").append(")=lower('").append(basicvalue.trim()).append("')");
			 builder.append(" or lower(a.TITLEN) =lower('").append(basicvalue.trim()).append("')");
	 		 builder.append(" or lower(a.").append("PERIODICALNAME").append(")=lower('").append(basicvalue.trim()).append("')");
			// builder.append(" or lower(a.PERIODICALNAMEENG) =lower('").append(basicvalue.trim()).append("')");
			// builder.append(" or lower(a.BOOKNAME) =lower('").append(basicvalue.trim()).append("')");
	 		 builder.append(" or lower(a.").append("ABSTRACT)").append("=lower('").append(basicvalue.trim()).append("')");
			// builder.append(" or lower(a.ABSTRACTENG) =lower('").append(basicvalue.trim()).append("')");
			// builder.append(" or lower(a.ORIGINALTEXT) =lower('").append(basicvalue.trim()).append("')");
	 		 builder.append(" or lower(a.").append("SUBJECTWORD)").append("=lower('").append(basicvalue.trim()).append("')");
			// builder.append(" or lower(a.SUBJECTWORDENG) =lower('").append(basicvalue.trim()).append("')");
	  		 builder.append(" or lower(a.").append("KEYWORD)").append("=lower('").append(basicvalue.trim()).append("')");
			// builder.append(" or lower(a.KEYWORDENG) =lower('").append(basicvalue.trim()).append("')");
			 builder.append(" or lower(a.AUTHOR) =lower('").append(basicvalue.trim()).append("')");
			 //builder.append(" or lower(a.YEARSTR) =lower('").append(basicvalue.trim()).append("')");
			 builder.append(" or lower(a.PERIOD) =lower('").append(basicvalue.trim()).append("')");
			 builder.append(" or lower(a.BOOKCD) =lower('").append(basicvalue.trim()).append("')");
			 builder.append(" or lower(a.FUNDNAME) =lower('").append(basicvalue.trim()).append("')");
			 builder.append(" or lower(a.FUNDTYPE) =lower('").append(basicvalue.trim()).append("')"); 
			 builder.append(" or lower(a.TCMNAME) =lower('").append(basicvalue.trim()).append("'))");
		 }
		 return builder.toString();
	 }
}
